package com.smart.community.commons.monitor;

import com.zaxxer.hikari.HikariDataSource;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Service;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.concurrent.atomic.AtomicLong;

/**
 * 数据库性能监控服务
 * 功能：
 * 1. 监控数据库连接池状态
 * 2. 监控数据库响应时间
 * 3. 收集数据库性能指标
 * 4. 提供性能告警
 * 
 * @author Wu.Liang
 */
@Slf4j
@Service
public class DatabasePerformanceMonitorService {

    @Autowired
    private DataSource dataSource;

    @Autowired
    private SqlExecutionTimeMonitor sqlExecutionTimeMonitor;

    // 性能告警阈值
    private static final int ACTIVE_CONNECTION_ALERT_THRESHOLD = 15; // 活跃连接数告警阈值
    private static final int CONNECTION_POOL_ALERT_THRESHOLD = 80; // 连接池使用率告警阈值80%
    private static final long RESPONSE_TIME_ALERT_THRESHOLD = 5000; // 响应时间告警阈值5秒

    // 性能指标计数器
    private final AtomicLong healthCheckCount = new AtomicLong(0);
    private final AtomicLong healthCheckTotalTime = new AtomicLong(0);
    private final AtomicLong responseTimeAlertCount = new AtomicLong(0);
    private final AtomicLong connectionPoolAlertCount = new AtomicLong(0);

    /**
     * 定期监控数据库性能
     * 定期输出性能报告
     */
    @Scheduled(fixedRate = 60000) // 每分钟执行一次
    public void monitorDatabasePerformance() {
        try {
            // 监控连接池状态
            DatabaseConnectionPoolInfo poolInfo = getConnectionPoolInfo();
            
            // 监控数据库响应时间
            DatabaseHealthInfo healthInfo = checkDatabaseHealth();
            
            // 检查告警条件
            checkAlerts(poolInfo, healthInfo);
            
            // 输出监控信息（DEBUG级别）
            if (log.isDebugEnabled()) {
                log.debug("📊 数据库性能监控 - 连接池使用率: {:.1f}%, 响应时间: {}ms, 活跃连接: {}",
                        poolInfo.getUsageRate(), healthInfo.getResponseTime(), poolInfo.getActiveConnections());
            }
            
        } catch (Exception e) {
            log.error("❌ 数据库性能监控出现异常", e);
        }
    }

    /**
     * 获取连接池信息
     */
    public DatabaseConnectionPoolInfo getConnectionPoolInfo() {
        DatabaseConnectionPoolInfo info = new DatabaseConnectionPoolInfo();
        
        try {
            if (dataSource instanceof HikariDataSource) {
                HikariDataSource hikariDataSource = (HikariDataSource) dataSource;
                
                info.setPoolName("HikariCP");
                info.setActiveConnections(hikariDataSource.getHikariPoolMXBean().getActiveConnections());
                info.setIdleConnections(hikariDataSource.getHikariPoolMXBean().getIdleConnections());
                info.setTotalConnections(hikariDataSource.getHikariPoolMXBean().getTotalConnections());
                info.setWaitingThreads(hikariDataSource.getHikariPoolMXBean().getThreadsAwaitingConnection());
                info.setMaximumPoolSize(hikariDataSource.getMaximumPoolSize());
                info.setMinimumIdle(hikariDataSource.getMinimumIdle());
                info.setConnectionTimeout(hikariDataSource.getConnectionTimeout());
                info.setIdleTimeout(hikariDataSource.getIdleTimeout());
                info.setMaxLifetime(hikariDataSource.getMaxLifetime());
                
                // 计算使用率
                double usageRate = (double) hikariDataSource.getHikariPoolMXBean().getActiveConnections() / hikariDataSource.getMaximumPoolSize() * 100;
                info.setUsageRate(usageRate);
                
                log.debug("✅ 连接池信息获取成功: {}", info.getPoolName());
            } else {
                log.warn("⚠️ 数据源类型不是HikariDataSource: {}", dataSource.getClass().getSimpleName());
                info.setPoolName("Unknown");
            }
        } catch (Exception e) {
            log.error("❌ 获取连接池信息失败", e);
            info.setPoolName("Error");
        }
        
        return info;
    }

    /**
     * 检查数据库健康状态
     */
    public DatabaseHealthInfo checkDatabaseHealth() {
        DatabaseHealthInfo info = new DatabaseHealthInfo();
        long startTime = System.currentTimeMillis();
        
        try (Connection connection = dataSource.getConnection();
             PreparedStatement statement = connection.prepareStatement("SELECT 1");
             ResultSet resultSet = statement.executeQuery()) {
            
            long responseTime = System.currentTimeMillis() - startTime;
            
            if (resultSet.next()) {
                info.setHealthy(true);
                info.setResponseTime(responseTime);
                info.setErrorMessage(null);
                
                // 更新统计
                healthCheckCount.incrementAndGet();
                healthCheckTotalTime.addAndGet(responseTime);
                
                log.debug("✅ 数据库健康检查通过，响应时间: {}ms", responseTime);
            } else {
                info.setHealthy(false);
                info.setResponseTime(responseTime);
                info.setErrorMessage("健康检查查询无结果");
                log.warn("⚠️ 数据库健康检查异常：查询无结果");
            }
            
        } catch (SQLException e) {
            long responseTime = System.currentTimeMillis() - startTime;
            info.setHealthy(false);
            info.setResponseTime(responseTime);
            info.setErrorMessage(e.getMessage());
            log.error("❌ 数据库健康检查失败，响应时间: {}ms", responseTime, e);
        } catch (Exception e) {
            long responseTime = System.currentTimeMillis() - startTime;
            info.setHealthy(false);
            info.setResponseTime(responseTime);
            info.setErrorMessage(e.getMessage());
            log.error("❌ 数据库健康检查失败，响应时间: {}ms", responseTime, e);
        }
        
        return info;
    }

    /**
     * 获取数据库性能报告
     */
    public DatabasePerformanceReport getPerformanceReport() {
        DatabasePerformanceReport report = new DatabasePerformanceReport();
        
        try {
            report.setGeneratedTime(LocalDateTime.now());
            report.setConnectionPoolInfo(getConnectionPoolInfo());
            report.setHealthInfo(checkDatabaseHealth());
            report.setHealthCheckCount(healthCheckCount.get());
            report.setResponseTimeAlertCount(responseTimeAlertCount.get());
            report.setConnectionPoolAlertCount(connectionPoolAlertCount.get());
            
            // 计算平均响应时间
            if (healthCheckCount.get() > 0) {
                double averageResponseTime = (double) healthCheckTotalTime.get() / healthCheckCount.get();
                report.setAverageResponseTime(averageResponseTime);
            }
            
            // 获取SQL执行报告
            if (sqlExecutionTimeMonitor != null) {
                SqlExecutionTimeMonitor.SqlExecutionReport sqlReport = sqlExecutionTimeMonitor.getExecutionReport();
                report.setSqlExecutionReport(sqlReport);
            }
            
        } catch (Exception e) {
            log.error("❌ 输出性能报告失败", e);
        }
        
        return report;
    }

    /**
     * 检查告警条件
     */
    private void checkAlerts(DatabaseConnectionPoolInfo poolInfo, DatabaseHealthInfo healthInfo) {
        // 检查活跃连接数告警
        if (poolInfo.getActiveConnections() > ACTIVE_CONNECTION_ALERT_THRESHOLD) {
            log.warn("🚨 活跃连接数告警: {} (阈值: {})",
                    poolInfo.getActiveConnections(), ACTIVE_CONNECTION_ALERT_THRESHOLD);
        }
        
        // 检查连接池使用率告警
        if (poolInfo.getUsageRate() > CONNECTION_POOL_ALERT_THRESHOLD) {
            log.warn("🚨 连接池使用率告警: {:.1f}% (阈值: {}%)",
                    poolInfo.getUsageRate(), CONNECTION_POOL_ALERT_THRESHOLD);
            connectionPoolAlertCount.incrementAndGet();
        }
        
        // 检查响应时间告警
        if (healthInfo.getResponseTime() > RESPONSE_TIME_ALERT_THRESHOLD) {
            log.warn("🚨 数据库响应时间告警: {}ms (阈值: {}ms)",
                    healthInfo.getResponseTime(), RESPONSE_TIME_ALERT_THRESHOLD);
            responseTimeAlertCount.incrementAndGet();
        }
        
        // 检查数据库健康状态
        if (!healthInfo.isHealthy()) {
            log.error("🚨 数据库健康状态告警: {}", healthInfo.getErrorMessage());
        }
    }

    /**
     * 重置监控统计
     */
    public void resetMonitoringStats() {
        healthCheckCount.set(0);
        healthCheckTotalTime.set(0);
        responseTimeAlertCount.set(0);
        connectionPoolAlertCount.set(0);
        log.info("📊 数据库性能监控统计已重置");
    }

    /**
     * 输出性能报告到日志
     */
    public void logPerformanceReport() {
        DatabasePerformanceReport report = getPerformanceReport();
        
        log.info("📊 === 数据库性能报告 ===");
        log.info("生成时间: {}", report.getGeneratedTime());
        log.info("数据库健康: {}", report.getHealthInfo().isHealthy() ? "正常" : "异常");
        log.info("  - 响应时间: {}ms", report.getHealthInfo().getResponseTime());
        log.info("  - 平均响应时间: {:.2f}ms", report.getAverageResponseTime());
        
        log.info("连接池状态: {}", report.getConnectionPoolInfo().getPoolName());
        log.info("  - 使用率: {:.1f}%", report.getConnectionPoolInfo().getUsageRate());
        log.info("  - 活跃连接: {}/{}",
                report.getConnectionPoolInfo().getActiveConnections(),
                report.getConnectionPoolInfo().getMaximumPoolSize());
        log.info("  - 等待线程: {}", report.getConnectionPoolInfo().getWaitingThreads());
        
        if (report.getSqlExecutionReport() != null) {
            SqlExecutionTimeMonitor.SqlExecutionReport sqlReport = report.getSqlExecutionReport();
            log.info("SQL执行统计:");
            log.info("  - 总执行次数: {}", sqlReport.getTotalExecutions());
            log.info("  - 平均执行时间: {:.2f}ms", sqlReport.getAverageExecutionTime());
            log.info("  - 慢查询次数: {}", sqlReport.getSlowQueryCount());
            log.info("  - 慢查询比率: {:.2f}%", sqlReport.getSlowQueryRate());
        }
        
        log.info("告警统计:");
        log.info("  - 响应时间告警次数: {}", report.getResponseTimeAlertCount());
        log.info("  - 连接池告警次数: {}", report.getConnectionPoolAlertCount());
        log.info("  - 健康检查次数: {}", report.getHealthCheckCount());
        log.info("📊 === 报告结束 ===");
    }

    // 内部类定义
    public static class DatabaseConnectionPoolInfo {
        private String poolName;
        private int activeConnections;
        private int idleConnections;
        private int totalConnections;
        private int waitingThreads;
        private int maximumPoolSize;
        private int minimumIdle;
        private long connectionTimeout;
        private long idleTimeout;
        private long maxLifetime;
        private double usageRate;

        // Getters and Setters
        public String getPoolName() { return poolName; }
        public void setPoolName(String poolName) { this.poolName = poolName; }
        
        public int getActiveConnections() { return activeConnections; }
        public void setActiveConnections(int activeConnections) { this.activeConnections = activeConnections; }
        
        public int getIdleConnections() { return idleConnections; }
        public void setIdleConnections(int idleConnections) { this.idleConnections = idleConnections; }
        
        public int getTotalConnections() { return totalConnections; }
        public void setTotalConnections(int totalConnections) { this.totalConnections = totalConnections; }
        
        public int getWaitingThreads() { return waitingThreads; }
        public void setWaitingThreads(int waitingThreads) { this.waitingThreads = waitingThreads; }
        
        public int getMaximumPoolSize() { return maximumPoolSize; }
        public void setMaximumPoolSize(int maximumPoolSize) { this.maximumPoolSize = maximumPoolSize; }
        
        public int getMinimumIdle() { return minimumIdle; }
        public void setMinimumIdle(int minimumIdle) { this.minimumIdle = minimumIdle; }
        
        public long getConnectionTimeout() { return connectionTimeout; }
        public void setConnectionTimeout(long connectionTimeout) { this.connectionTimeout = connectionTimeout; }
        
        public long getIdleTimeout() { return idleTimeout; }
        public void setIdleTimeout(long idleTimeout) { this.idleTimeout = idleTimeout; }
        
        public long getMaxLifetime() { return maxLifetime; }
        public void setMaxLifetime(long maxLifetime) { this.maxLifetime = maxLifetime; }
        
        public double getUsageRate() { return usageRate; }
        public void setUsageRate(double usageRate) { this.usageRate = usageRate; }
    }

    public static class DatabaseHealthInfo {
        private boolean healthy;
        private long responseTime;
        private String errorMessage;
        private LocalDateTime timestamp;

        public DatabaseHealthInfo() {
            this.timestamp = LocalDateTime.now();
        }

        // Getters and Setters
        public boolean isHealthy() { return healthy; }
        public void setHealthy(boolean healthy) { this.healthy = healthy; }
        
        public long getResponseTime() { return responseTime; }
        public void setResponseTime(long responseTime) { this.responseTime = responseTime; }
        
        public String getErrorMessage() { return errorMessage; }
        public void setErrorMessage(String errorMessage) { this.errorMessage = errorMessage; }
        
        public LocalDateTime getTimestamp() { return timestamp; }
        public void setTimestamp(LocalDateTime timestamp) { this.timestamp = timestamp; }
    }

    public static class DatabasePerformanceReport {
        private LocalDateTime generatedTime;
        private DatabaseConnectionPoolInfo connectionPoolInfo;
        private DatabaseHealthInfo healthInfo;
        private double averageResponseTime;
        private long healthCheckCount;
        private long responseTimeAlertCount;
        private long connectionPoolAlertCount;
        private SqlExecutionTimeMonitor.SqlExecutionReport sqlExecutionReport;

        // Getters and Setters
        public LocalDateTime getGeneratedTime() { return generatedTime; }
        public void setGeneratedTime(LocalDateTime generatedTime) { this.generatedTime = generatedTime; }
        
        public DatabaseConnectionPoolInfo getConnectionPoolInfo() { return connectionPoolInfo; }
        public void setConnectionPoolInfo(DatabaseConnectionPoolInfo connectionPoolInfo) { this.connectionPoolInfo = connectionPoolInfo; }
        
        public DatabaseHealthInfo getHealthInfo() { return healthInfo; }
        public void setHealthInfo(DatabaseHealthInfo healthInfo) { this.healthInfo = healthInfo; }
        
        public double getAverageResponseTime() { return averageResponseTime; }
        public void setAverageResponseTime(double averageResponseTime) { this.averageResponseTime = averageResponseTime; }
        
        public long getHealthCheckCount() { return healthCheckCount; }
        public void setHealthCheckCount(long healthCheckCount) { this.healthCheckCount = healthCheckCount; }
        
        public long getResponseTimeAlertCount() { return responseTimeAlertCount; }
        public void setResponseTimeAlertCount(long responseTimeAlertCount) { this.responseTimeAlertCount = responseTimeAlertCount; }
        
        public long getConnectionPoolAlertCount() { return connectionPoolAlertCount; }
        public void setConnectionPoolAlertCount(long connectionPoolAlertCount) { this.connectionPoolAlertCount = connectionPoolAlertCount; }
        
        public SqlExecutionTimeMonitor.SqlExecutionReport getSqlExecutionReport() { return sqlExecutionReport; }
        public void setSqlExecutionReport(SqlExecutionTimeMonitor.SqlExecutionReport sqlExecutionReport) { this.sqlExecutionReport = sqlExecutionReport; }
    }
}
